﻿-- Create the new table
CREATE TABLE [main].[OnlineStatus] (
    [DeviceId] integer NOT NULL,
    [TimeStamp] datetime NOT NULL DEFAULT current_timestamp,
    [StatusOld] varchar(50),
    [StatusNew] varchar(50),
    CONSTRAINT [FK_OnlineStatus_DeviceId_Devices_Id] FOREIGN KEY ([DeviceId]) REFERENCES [Devices] ([Id])
);

-- Create the new indexes
CREATE INDEX [main].[IX_DeviceId_TimeStamp] ON [OnlineStatus] ([DeviceId], [TimeStamp] DESC);

-- Alter trigger
DROP TRIGGER [main].[TRG_DeleteTraffic];

CREATE TRIGGER [main].[TRG_DeleteTraffic] AFTER DELETE ON [main].[Devices] FOR EACH ROW
BEGIN
	delete from Traffic where Traffic.DeviceId=old.Id;
	delete from OnlineStatus where OnlineStatus.DeviceId=old.Id;
END;
